package org.gdstash.db;

import org.gdstash.description.BonusDetail;
import org.gdstash.util.GDMsgFormatter;
import org.gdstash.util.GDMsgLogger;

import java.sql.*;
import java.util.LinkedList;
import java.util.List;

public class DBSkillModifier {
    public static final String TABLE_NAME_ITEM = "GD_ITEM_SKILLMODIFIER";
    public static final String TABLE_NAME_ITEMSET = "GD_ITEMSET_SKILLMODIFIER";
    public static final String TABLE_NAME_AFFIX = "GD_AFFIX_SKILLMODIFIER";
    public static final String FIELD_ID_ITEM = "ITEM_ID";
    public static final String FIELD_ID_ITEMSET = "ITEMSET_ID";
    private static final String FIELD_ID_AFFIX = "AFFIX_ID";
    private static final int ROW_ITEM_ID = 1;
    private static final int ROW_MODIFIER_ID = 2;
    private static final int ROW_SKILL_ID = 3;
    private static final int ROW_MASTERY_ID = 4;
    private String itemID;
    private String modifierID;
    private String skillID;
    private String masteryID;
    private int index;
    private DBSkill dbSkill;
    private DBSkill dbModifier;
    private String masteryName;

    public int getIndex() {
        return this.index;
    }

    public void setIndex(final int index) {
        this.index = index;
    }

    public String getSkillID() {
        return this.skillID;
    }

    public void setSkillID(final String skillID) {
        this.skillID = skillID;
    }

    public String getSkillName() {
        if (this.dbSkill == null) {
            return null;
        }
        return this.dbSkill.getName();
    }

    public String getMasteryName() {
        return this.masteryName;
    }

    public DBSkill getSkill() {
        return this.dbSkill;
    }

    public String getModifierID() {
        return this.modifierID;
    }

    public void setModifierID(final String modifierID) {
        this.modifierID = modifierID;
    }

    public List<DBStat> getModifierStatList() {
        List<DBStat> list = null;
        if (this.dbModifier == null) {
            list = new LinkedList<DBStat>();
        } else {
            list = this.dbModifier.getStatList();
        }
        return list;
    }

    public DBSkill getSkillModifier() {
        return this.dbModifier;
    }

    public static void createItemTable(final Connection conn) throws SQLException {
        createTable(conn, "GD_ITEM_SKILLMODIFIER", "ITEM_ID");
    }

    public static void createItemSetTable(final Connection conn) throws SQLException {
        createTable(conn, "GD_ITEMSET_SKILLMODIFIER", "ITEMSET_ID");
    }

    public static void createAffixTable(final Connection conn) throws SQLException {
        createTable(conn, "GD_AFFIX_SKILLMODIFIER", "AFFIX_ID");
    }

    private static void createTable(final Connection conn, final String tabName, final String idName) throws SQLException {
        final String dropTable = "DROP TABLE " + tabName;
        final String createTable = "CREATE TABLE " + tabName + " (" + idName + "       VARCHAR(" + "256" + ") NOT NULL, MODIFIER_ID     VARCHAR(" + "256" + ") NOT NULL, SKILL_ID        VARCHAR(" + "256" + ") NOT NULL, MASTERY_ID      VARCHAR(" + "256" + ") , PRIMARY KEY (" + idName + ", MODIFIER_ID, SKILL_ID))";
        try (final Statement st = conn.createStatement()) {
            if (GDDBUtil.tableExists(conn, tabName)) {
                st.execute(dropTable);
            }
            st.execute(createTable);
            st.close();
            conn.commit();
        } catch (SQLException ex) {
            final Object[] args = {tabName};
            final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_CREATE_TABLE", args);
            GDMsgLogger.addError(msg);
            throw ex;
        }
    }

    public static void deleteItem(final Connection conn, final String id) throws SQLException {
        delete(conn, "GD_ITEM_SKILLMODIFIER", "ITEM_ID", id);
    }

    public static void deleteItemSet(final Connection conn, final String id) throws SQLException {
        delete(conn, "GD_ITEMSET_SKILLMODIFIER", "ITEMSET_ID", id);
    }

    public static void deleteAffix(final Connection conn, final String id) throws SQLException {
        delete(conn, "GD_AFFIX_SKILLMODIFIER", "AFFIX_ID", id);
    }

    private static void delete(final Connection conn, final String tabName, final String idName, final String id) throws SQLException {
        final String deleteEntry = "DELETE FROM " + tabName + " WHERE " + idName + " = ?";
        try (final PreparedStatement ps = conn.prepareStatement(deleteEntry)) {
            ps.setString(1, id);
            ps.executeUpdate();
            ps.close();
        } catch (SQLException ex) {
            throw ex;
        }
    }

    public static void insertItem(final Connection conn, final String id, final List<DBSkillModifier> modifiers) throws SQLException {
        insert(conn, "GD_ITEM_SKILLMODIFIER", id, modifiers);
    }

    public static void insertItemSet(final Connection conn, final String id, final List<DBSkillModifier> modifiers) throws SQLException {
        insert(conn, "GD_ITEMSET_SKILLMODIFIER", id, modifiers);
    }

    public static void insertAffix(final Connection conn, final String id, final List<DBSkillModifier> modifiers) throws SQLException {
        insert(conn, "GD_AFFIX_SKILLMODIFIER", id, modifiers);
    }

    private static void insert(final Connection conn, final String tabName, final String id, final List<DBSkillModifier> modifiers) throws SQLException {
        final String insert = "INSERT INTO " + tabName + " VALUES (?,?,?,?)";
        if (modifiers == null) {
            return;
        }
        if (modifiers.isEmpty()) {
            return;
        }
        try (final PreparedStatement ps = conn.prepareStatement(insert)) {
            for (final DBSkillModifier modifier : modifiers) {
                if (modifier.skillID == null) {
                    final Object[] args = {id, tabName};
                    final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_INS_TABLE_BY_ID", args);
                    GDMsgLogger.addWarning(msg);
                } else if (modifier.modifierID == null) {
                    final Object[] args = {id, tabName};
                    final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_INS_TABLE_BY_ID", args);
                    GDMsgLogger.addWarning(msg);
                } else {
                    try {
                        ps.setString(1, id);
                        ps.setString(2, modifier.modifierID);
                        ps.setString(3, modifier.skillID);
                        ps.setString(4, modifier.masteryID);
                        ps.executeUpdate();
                        ps.clearParameters();
                    } catch (SQLException ex) {
                        final Object[] args2 = {id, "GD_ITEM"};
                        final String msg2 = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_INS_TABLE_BY_ID", args2);
                        GDMsgLogger.addWarning(msg2);
                        GDMsgLogger.addWarning(ex);
                    }
                }
            }
            ps.close();
        }
    }

    public static List<DBSkillModifier> getItem(final String id) throws SQLException {
        return get("GD_ITEM_SKILLMODIFIER", "ITEM_ID", id);
    }

    public static List<DBSkillModifier> getItemSet(final String id) throws SQLException {
        return get("GD_ITEMSET_SKILLMODIFIER", "ITEMSET_ID", id);
    }

    public static List<DBSkillModifier> getAffix(final String id) throws SQLException {
        return get("GD_AFFIX_SKILLMODIFIER", "AFFIX_ID", id);
    }

    private static List<DBSkillModifier> get(final String tableName, final String idName, final String id) {
        List<DBSkillModifier> list = new LinkedList<DBSkillModifier>();
        final String command = "SELECT * FROM " + tableName + " WHERE " + idName + " = ?";
        try (final Connection conn = GDDBData.getConnection();
             final PreparedStatement ps = conn.prepareStatement(command)) {
            ps.setString(1, id);
            try (final ResultSet rs = ps.executeQuery()) {
                list = wrap(rs);
                conn.commit();
            } catch (SQLException ex) {
                throw ex;
            }
        } catch (SQLException ex2) {
            final Object[] args = {id, tableName};
            final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_READ_TABLE_BY_ID", args);
            GDMsgLogger.addError(msg);
            GDMsgLogger.addError(ex2);
        }
        return list;
    }

    public static List<DBSkillModifier> getAllItem() throws SQLException {
        return getAll("GD_ITEM_SKILLMODIFIER");
    }

    public static List<DBSkillModifier> getAllAffix() throws SQLException {
        return getAll("GD_AFFIX_SKILLMODIFIER");
    }

    private static List<DBSkillModifier> getAll(final String tableName) {
        List<DBSkillModifier> list = new LinkedList<DBSkillModifier>();
        final String command = "SELECT * FROM " + tableName;
        try (final Connection conn = GDDBData.getConnection();
             final PreparedStatement ps = conn.prepareStatement(command)) {
            try (final ResultSet rs = ps.executeQuery()) {
                list = wrap(rs);
                conn.commit();
            } catch (SQLException ex) {
                throw ex;
            }
        } catch (SQLException ex2) {
            final Object[] args = {"*", tableName};
            final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_READ_TABLE_BY_ID", args);
            GDMsgLogger.addError(msg);
            GDMsgLogger.addError(ex2);
        }
        return list;
    }

    private static List<DBSkillModifier> wrap(final ResultSet rs) throws SQLException {
        final LinkedList<DBSkillModifier> list = new LinkedList<DBSkillModifier>();
        while (rs.next()) {
            final DBSkillModifier modifier = new DBSkillModifier();
            modifier.itemID = rs.getString(1);
            modifier.modifierID = rs.getString(2);
            modifier.skillID = rs.getString(3);
            modifier.masteryID = rs.getString(4);
            if (modifier.modifierID != null) {
                modifier.dbModifier = DBSkill.get(modifier.modifierID);
            }
            if (modifier.skillID != null) {
                modifier.dbSkill = DBSkill.get(modifier.skillID);
            }
            if (modifier.dbSkill != null) {
                final DBSkillTree skillTree = DBSkillTree.getBySkillID(modifier.dbSkill.getSkillID());
                if (skillTree != null) {
                    final DBSkill mastery = skillTree.getMasterySkill();
                    if (mastery != null) {
                        modifier.masteryName = mastery.getName();
                    }
                }
            }
            list.add(modifier);
        }
        return list;
    }

    public static void updateDB() {
        updateItemDB();
        updateItemSetDB();
        updateAffixDB();
    }

    private static void updateItemDB() {
        updateDB("GD_ITEM_SKILLMODIFIER", "ITEM_ID");
    }

    private static void updateItemSetDB() {
        updateDB("GD_ITEMSET_SKILLMODIFIER", "ITEMSET_ID");
    }

    private static void updateAffixDB() {
        updateDB("GD_AFFIX_SKILLMODIFIER", "AFFIX_ID");
    }

    private static void updateDB(final String tableName, final String fieldName) {
        final List<DBSkillModifier> list = getEmptyMasteries(tableName);
        if (list == null) {
            return;
        }
        for (final DBSkillModifier modifier : list) {
            if (modifier.skillID == null) {
                continue;
            }
            final DBSkill skill = DBSkill.get(modifier.skillID);
            if (skill == null) {
                final Object[] args = {tableName, modifier.itemID, modifier.skillID, "GD_SKILL"};
                final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_REF_TABLE_ID_FROM_ID", args);
                GDMsgLogger.addWarning(msg);
            } else {
                modifier.masteryID = skill.getMasteryID();
            }
        }
        updateMastery(tableName, fieldName, list);
    }

    private static List<DBSkillModifier> getEmptyMasteries(final String tableName) {
        List<DBSkillModifier> list = new LinkedList<DBSkillModifier>();
        final String command = "SELECT * FROM " + tableName + " WHERE MASTERY_ID is null";
        try (final Connection conn = GDDBData.getConnection();
             final PreparedStatement ps = conn.prepareStatement(command)) {
            try (final ResultSet rs = ps.executeQuery()) {
                list = wrap(rs);
                conn.commit();
            } catch (SQLException ex) {
                throw ex;
            }
        } catch (SQLException ex2) {
            final Object[] args = {"MASTERY_ID is null", tableName};
            final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_READ_TABLE_BY_ID", args);
            GDMsgLogger.addError(msg);
            GDMsgLogger.addError(ex2);
        }
        return list;
    }

    private static void updateMastery(final String tableName, final String fieldName, final List<DBSkillModifier> list) {
        final String command = "UPDATE " + tableName + " SET MASTERY_ID = ? WHERE " + fieldName + " = ? AND MODIFIER_ID = ? AND SKILL_ID = ?";
        try (final Connection conn = GDDBData.getConnection();
             final PreparedStatement ps = conn.prepareStatement(command)) {
            for (final DBSkillModifier modifier : list) {
                if (modifier.masteryID == null) {
                    continue;
                }
                ps.setString(1, modifier.masteryID);
                ps.setString(2, modifier.itemID);
                ps.setString(3, modifier.modifierID);
                ps.setString(4, modifier.skillID);
                ps.executeUpdate();
                ps.clearParameters();
            }
            ps.close();
            conn.commit();
        } catch (SQLException ex) {
            final Object[] args = {"-", tableName};
            final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_READ_TABLE_BY_ID", args);
            GDMsgLogger.addError(msg);
            GDMsgLogger.addError(ex);
        }
    }

    public BonusDetail getBonusDetail(final DBStashItem item, final int detailType, final String prefix) {
        BonusDetail bonus = null;
        if (this.dbModifier == null) {
            return bonus;
        }
        bonus = new BonusDetail(this);
        return bonus;
    }
}
